#!/usr/bin/env Jython
#-*- coding:utf-8 -*-

import sys
import os
import csv
import re

from com.ziclix.python.sql import zxJDBC
from java.lang import Class
from java.sql  import DriverManager, SQLException

DATABASE    = "dbByPython.db"
JDBC_URL    = "jdbc:sqlite:%s"  % DATABASE
JDBC_DRIVER = "org.sqlite.JDBC"

TABLE_NAME      = "rebides"
TABLE_DROPPER   = "drop table if exists %s;"                           % TABLE_NAME
TABLE_CREATOR   = "create table %s (Codigo_do_Docente, Nome, Ultimo_Grau, Curso, Categoria, Regime_de_Tempo, Ano_Lectivo, Tipo_de_Estabelecimento, Nome_do_Estabelecimento);"       % TABLE_NAME
RECORD_INSERTER = "insert into %s values (?, ?, ?, ?, ?, ?, ?, ?, ?);" % TABLE_NAME



def main():
    dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    stmt = dbConn.createStatement()
    try:
        stmt.executeUpdate(TABLE_DROPPER)
        stmt.executeUpdate(TABLE_CREATOR)
    except SQLException, msg:
        print msg
        sys.exit(1)

    for i in xrange(0,11):
        csvName = 'reb%d.csv' % i
        PLANET_DATA = csv.reader(open(csvName, 'rb'), delimiter=',')
        populateTable(dbConn, PLANET_DATA)
   
    stmt.close()
    dbConn.close()
    sys.exit(0)
    
#####################################################################################################
#####################################################################################################

def getConnection(jdbc_url, driverName):
    """
        Given the name of a JDBC driver class and the url to be used 
        to connect to a database, attempt to obtain a connection to 
        the database.
    """
    try:
        Class.forName(driverName).newInstance()
    except Exception, msg:
        print msg
        sys.exit(-1)

    try:
        dbConn = DriverManager.getConnection(jdbc_url)
    except SQLException, msg:
        print msg
        sys.exit(-1)

    return dbConn

################################################################################

def populateTable(dbConn, feedstock):
    """
        Given an open connection to a SQLite database and a list of tuples
        with the data to be inserted, insert the data into the target table.
    """
    try:
        preppedStmt = dbConn.prepareStatement(RECORD_INSERTER)
        for row in feedstock:
            if(row[1] == 'Nome'):
                continue
            else:
                preppedStmt.setString(1, unicode(row[0], 'utf-8'))
                preppedStmt.setString(2, unicode(row[1], 'utf-8'))
                preppedStmt.setString(3, unicode(row[2], 'utf-8'))
                preppedStmt.setString(4, unicode(row[3], 'utf-8'))
                preppedStmt.setString(5, unicode(row[4], 'utf-8'))
                preppedStmt.setString(6, unicode(row[5], 'utf-8'))
                preppedStmt.setString(7, unicode(row[6], 'utf-8'))
                preppedStmt.setString(8, unicode(row[7], 'utf-8'))
                preppedStmt.setString(9, unicode(row[8], 'utf-8'))
                preppedStmt.addBatch()
        dbConn.setAutoCommit(False)
        preppedStmt.executeBatch()
        dbConn.setAutoCommit(True)
    except SQLException, msg:
        print msg
        return False

    return True

